package df.bean.db.table;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;

import df.bean.db.conn.DBConnection;
import df.bean.frame.CalculatorFrm1;

public class TrnDetail extends ABSTable {
    private String hospitalCode = "";
    private String invoiceNo = "";
    private String invoiceDate = "";
    private String receiptNo = "";
    private String receiptDate = "";
    private String lineNo = "";
    private String orderItemCode = "";
    private String doctorCode = "";
    private String departmentGroupCode = "";
    private String locationCode = "";
    private Double amountBefDiscount = 0d;
    private Double amountOfDiscount = 0d;
    private Double amountAftDiscount = 0d;
    private String batchNo = "";
    private String statusModify = "";
    private String updateDate = "";
    private String updateTime = "";
    private String userId = "";
    private String oldDoctorCode = "";
    private String invIsVoid = "";
    private String yyyy = "";
    private String mm = "";
    private String dd = "";
    private String receiptTypeCode = "";
    private String receiptModule = "";
    private String receiptType = "";
    private String recIsVoid = "";
    private Double recAmountBefDiscount = 0d;
    private Double recAmountOfDiscount = 0d;
    private Double recPremiumAmt = 0d;
    private String receiptModeCode;
    private Double norAllocateAmt;
    private Double norAllocatePct;
    private Double drAmt;
    private Double drAmt402;
    private Double drAmt406;
    private Double drPremium;
    private Double drTaxBase;
    private Double hpAmt;
    private Double hpPremium;
    private Double hpTax;
    private String taxTypeCode;
    private String admissionTypeCode;
    private String doctorTreatmentCode;
    private String computeDailyDate;
    private String computeDailyTime;
    private String computeDailyUserID;
    private Double premiumChargePct;
    private Double premiumVatPct;
    private String transactionDate;
    private String doctorCategoryCode;
    private String excludeTreatment;
    private String computeMonthlyDate;
    private String computeMonthlyTime;
    private String computeMonthlyUserID;
    private Double premiumRecAmt=0d;
    private String ACTIVE = "";
    
    private String tableName = "TRN_DAILY";
    private Statement stmt = null;
    private ResultSet rs = null;
    
    public TrnDetail() {
    }
    public TrnDetail(DBConnection conn) {
        super();
        this.setDBConnection(conn);
        this.setStmt(this.getDBConnection().getStatementForInsert());
    }

    public String getHospitalCode() {
        return hospitalCode;
    }

    public void setHospitalCode(String hospitalCode) {
        this.hospitalCode = hospitalCode;
    }

    public String getInvoiceNo() {
        return invoiceNo;
    }

    public void setInvoiceNo(String invoiceNo) {
        this.invoiceNo = invoiceNo;
    }

    public String getInvoiceDate() {
        return invoiceDate;
    }

    public void setInvoiceDate(String invoiceDate) {
        this.invoiceDate = invoiceDate;
    }

    public String getReceiptNo() {
        return receiptNo;
    }

    public void setReceiptNo(String receiptNo) {
        this.receiptNo = receiptNo;
    }

    public String getReceiptDate() {
        return receiptDate;
    }

    public void setReceiptDate(String receiptDate) {
        this.receiptDate = receiptDate;
    }

    public String getBillingSubGroup() {
        return lineNo;
    }

    public void setBillingSubGroup(String lineNo) {
        this.lineNo = lineNo;
    }

    public String getOrderItemCode() {
        return orderItemCode;
    }

    public void setOrderItemCode(String orderItemCode) {
        this.orderItemCode = orderItemCode;
    }

    public String getDoctorCode() {
        return doctorCode;
    }

    public void setDoctorCode(String doctorCode) {
        this.doctorCode = doctorCode;
    }

    public String getDepartmentGroupCode() {
        return departmentGroupCode;
    }

    public void setDepartmentGroupCode(String departmentGroupCode) {
        this.departmentGroupCode = departmentGroupCode;
    }

    public String getLocationCode() {
        return locationCode;
    }

    public void setLocationCode(String locationCode) {
        this.locationCode = locationCode;
    }

    public Double getAmountBefDiscount() {
        return amountBefDiscount;
    }

    public void setAmountBefDiscount(Double amountBefDiscount) {
        this.amountBefDiscount = amountBefDiscount;
    }

    public Double getAmountOfDiscount() {
        return amountOfDiscount;
    }

    public void setAmountOfDiscount(Double amountOfDiscount) {
        this.amountOfDiscount = amountOfDiscount;
    }

    public Double getAmountAftDiscount() {
        return amountAftDiscount;
    }

    public void setAmountAftDiscount(Double amountAftDiscount) {
        this.amountAftDiscount = amountAftDiscount;
    }

    public String getBatchNo() {
        return batchNo;
    }

    public void setBatchNo(String batchNo) {
        this.batchNo = batchNo;
    }

    public String getStatusModify() {
        return statusModify;
    }

    public void setStatusModify(String statusModify) {
        this.statusModify = statusModify;
    }

    public String getUpdateDate() {
        return updateDate;
    }

    public void setUpdateDate(String updateDate) {
        this.updateDate = updateDate;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getOldDoctorCode() {
        return oldDoctorCode;
    }

    public void setOldDoctorCode(String oldDoctorCode) {
        this.oldDoctorCode = oldDoctorCode;
    }

    public String getInvIsVoid() {
        return invIsVoid;
    }

    public void setInvIsVoid(String invIsVoid) {
        this.invIsVoid = invIsVoid;
    }

    public String getYyyy() {
        return yyyy;
    }

    public void setYyyy(String yyyy) {
        this.yyyy = yyyy;
    }

    public String getMm() {
        return mm;
    }

    public void setMm(String mm) {
        this.mm = mm;
    }

    public String getDd() {
        return dd;
    }

    public void setDd(String dd) {
        this.dd = dd;
    }

    public String getReceiptTypeCode() {
        return receiptTypeCode;
    }

    public void setReceiptTypeCode(String receiptTypeCode) {
        this.receiptTypeCode = receiptTypeCode;
    }

    public String getReceiptModule() {
        return receiptModule;
    }

    public void setReceiptModule(String receiptModule) {
        this.receiptModule = receiptModule;
    }

    public String getReceiptType() {
        return receiptType;
    }

    public void setReceiptType(String receiptType) {
        this.receiptType = receiptType;
    }

    public String getRecIsVoid() {
        return recIsVoid;
    }

    public void setRecIsVoid(String recIsVoid) {
        this.recIsVoid = recIsVoid;
    }

    public Double getRecAmountBefDiscount() {
        return recAmountBefDiscount;
    }

    public void setRecAmountBefDiscount(Double recAmountBefDiscount) {
        this.recAmountBefDiscount = recAmountBefDiscount;
    }

    public Double getRecAmountOfDiscount() {
        return recAmountOfDiscount;
    }

    public void setRecAmountOfDiscount(Double recAmountOfDiscount) {
        this.recAmountOfDiscount = recAmountOfDiscount;
    }

    public Double getRecPremiumAmt() {
        return recPremiumAmt;
    }

    public void setRecPremiumAmt(Double recPremiumAmt) {
        this.recPremiumAmt = recPremiumAmt;
    }

    public String getReceiptModeCode() {
        return receiptModeCode;
    }

    public void setReceiptModeCode(String receiptModeCode) {
        this.receiptModeCode = receiptModeCode;
    }

    public Double getNorAllocateAmt() {
        return norAllocateAmt;
    }

    public void setNorAllocateAmt(Double norAllocateAmt) {
        this.norAllocateAmt = norAllocateAmt;
    }

    public Double getNorAllocatePct() {
        return norAllocatePct;
    }

    public void setNorAllocatePct(Double norAllocatePct) {
        this.norAllocatePct = norAllocatePct;
    }

    public Double getDrAmt() {
        return drAmt;
    }

    public void setDrAmt(Double drAmt) {
        this.drAmt = drAmt;
    }

    public Double getDrAmt402() {
        return drAmt402;
    }

    public void setDrAmt402(Double drAmt402) {
        this.drAmt402 = drAmt402;
    }

    public Double getDrAmt406() {
        return drAmt406;
    }

    public void setDrAmt406(Double drAmt406) {
        this.drAmt406 = drAmt406;
    }

    public Double getDrPremium() {
        return drPremium;
    }

    public void setDrPremium(Double drPremium) {
        this.drPremium = drPremium;
    }

    public Double getDrTaxBase() {
        return drTaxBase;
    }

    public void setDrTaxBase(Double drTaxBase) {
        this.drTaxBase = drTaxBase;
    }

    public Double getHpAmt() {
        return hpAmt;
    }

    public void setHpAmt(Double hpAmt) {
        this.hpAmt = hpAmt;
    }

    public Double getHpPremium() {
        return hpPremium;
    }

    public void setHpPremium(Double hpPremium) {
        this.hpPremium = hpPremium;
    }

    public Double getHpTax() {
        return hpTax;
    }

    public void setHpTax(Double hpTax) {
        this.hpTax = hpTax;
    }

    public String getTaxTypeCode() {
        return taxTypeCode;
    }

    public void setTaxTypeCode(String taxTypeCode) {
        this.taxTypeCode = taxTypeCode;
    }

    public String getAdmissionTypeCode() {
        return admissionTypeCode;
    }

    public void setAdmissionTypeCode(String admissionTypeCode) {
        this.admissionTypeCode = admissionTypeCode;
    }

    public String getDoctorTreatmentCode() {
        return doctorTreatmentCode;
    }

    public void setDoctorTreatmentCode(String doctorTreatmentCode) {
        this.doctorTreatmentCode = doctorTreatmentCode;
    }

    public String getComputeDailyDate() {
        return computeDailyDate;
    }

    public void setComputeDailyDate(String computeDailyDate) {
        this.computeDailyDate = computeDailyDate;
    }

    public String getComputeDailyTime() {
        return computeDailyTime;
    }

    public void setComputeDailyTime(String computeDailyTime) {
        this.computeDailyTime = computeDailyTime;
    }

    public String getComputeDailyUserID() {
        return computeDailyUserID;
    }

    public void setComputeDailyUserID(String computeDailyUserID) {
        this.computeDailyUserID = computeDailyUserID;
    }

    public Double getPremiumChargePct() {
        return premiumChargePct;
    }

    public void setPremiumChargePct(Double premiumChargePct) {
        this.premiumChargePct = premiumChargePct;
    }

    public Double getPremiumVatPct() {
        return premiumVatPct;
    }

    public void setPremiumVatPct(Double premiumVatPct) {
        this.premiumVatPct = premiumVatPct;
    }

    public String getTransactionDate() {
        return transactionDate;
    }

    public void setTransactionDate(String transactionDate) {
        this.transactionDate = transactionDate;
    }

    public String getDoctorCategoryCode() {
        return doctorCategoryCode;
    }

    public void setDoctorCategoryCode(String doctorCategoryCode) {
        this.doctorCategoryCode = doctorCategoryCode;
    }

    public String getExcludeTreatment() {
        return excludeTreatment;
    }

    public void setExcludeTreatment(String excludeTreatment) {
        this.excludeTreatment = excludeTreatment;
    }

    public String getComputeMonthlyDate() {
        return computeMonthlyDate;
    }

    public void setComputeMonthlyDate(String computeMonthlyDate) {
        this.computeMonthlyDate = computeMonthlyDate;
    }

    public String getComputeMonthlyTime() {
        return computeMonthlyTime;
    }

    public void setComputeMonthlyTime(String computeMonthlyTime) {
        this.computeMonthlyTime = computeMonthlyTime;
    }

    public String getComputeMonthlyUserID() {
        return computeMonthlyUserID;
    }

    public void setComputeMonthlyUserID(String computeMonthlyUserID) {
        this.computeMonthlyUserID = computeMonthlyUserID;
    }

    public Double getPremiumRecAmt() {
        return premiumRecAmt;
    }

    public void setPremiumRecAmt(Double premiumRecAmt) {
        this.premiumRecAmt = premiumRecAmt;
    }

    public String getTableName() {
        return tableName;
    }

    public void setTableName(String tableName) {
        this.tableName = tableName;
    }

    public Statement getStmt() {
        return stmt;
    }

    public void setStmt(Statement stmt) {
        this.stmt = stmt;
    }

    public ResultSet getRs() {
        return rs;
    }

    public void setRs(ResultSet rs) {
        this.rs = rs;
    }

    public String getActive() {
        return ACTIVE;
    }

    public void setActive(String ACTIVE) {
        this.ACTIVE = ACTIVE;
    }
    
    
    public boolean update() {
    boolean ret = false;
        String sql = "";
            sql = "SELECT HOSPITAL_CODE,INVOICE_NO,INVOICE_DATE,BILLING_SUB_GROUP_NO,ORDER_ITEM_CODE,DOCTOR_CODE,ORDER_DATE,DEPARTMENT_GROUP_CODE,LOCATION_CODE,ADMISSION_TYPE_CODE,AMOUNT_BEF_DISCOUNT,AMOUNT_OF_DISCOUNT,AMOUNT_AFT_DISCOUNT,BATCH_NO,STATUS_MODIFY,UPDATE_DATE,UPDATE_TIME,USER_ID,OLD_DOCTOR_CODE,INV_IS_VOID,YYYY,MM,DD,RECEIPT_NO,RECEIPT_DATE,RECEIPT_MODULE,RECEIPT_TYPE,RECEIPT_TYPE_CODE,RECEIPT_MODE_CODE,REC_IS_VOID,REC_AMOUNT_BEF_DISCOUNT,REC_AMOUNT_OF_DISCOUNT,REC_PREMIUM_AMT,NOR_ALLOCATE_AMT,NOR_ALLOCATE_PCT,DR_AMT,DR_AMT_402,DR_AMT_406,DR_PREMIUM,DR_TAX_BASE,HP_AMT,HP_PREMIUM,HP_TAX,TAX_TYPE_CODE,DOCTOR_TREATMENT_CODE,COMPUTE_DAILY_DATE,COMPUTE_DAILY_TIME,COMPUTE_DAILY_USER_ID,PREMIUM_CHARGE_PCT,PREMIUM_VAT_PCT,TRANSACTION_DATE,DOCTOR_CATEGORY_CODE,EXCLUDE_TREATMENT, PREMIUM_REC_AMT,ACTIVE FROM " + this.getTableName(); 
            sql = sql + " where HOSPITAL_CODE='" + this.getHospitalCode() + "'";
            sql = sql + " and INVOICE_NO='" + this.getInvoiceNo() + "'";
            sql = sql + " and INVOICE_DATE = '" + this.getInvoiceDate() + "'";
            sql = sql + " and RECEIPT_NO = '" + this.getReceiptNo() + "'";
            sql = sql + " and RECEIPT_DATE = '" + this.getReceiptDate() + "'";
            sql = sql + " and BILLING_SUB_GROUP_NO = '" + this.getBillingSubGroup() + "'";
            sql = sql + " and ORDER_ITEM_CODE = '" + this.getOrderItemCode() + "'";
            sql = sql + " and INV_IS_VOID = '" + this.getInvIsVoid() + "'";
            sql = sql + " and REC_IS_VOID = '" + this.getRecIsVoid() + "'";
    
    try {
        if (rs == null) {

            rs = stmt.executeQuery(sql);
        } 

            // Move cursor to the "update row"
/*            if (this.getRs() == null) {
               try {
                   this.setRs(this.getStmt().executeQuery(sql));
               } catch (SQLException e) {                e.printStackTrace();            }
            }
            */
            rs.absolute(1); 
//            this.getRs().moveToCurrentRow();
/*            this.getRs().updateString("hospital_Code", this.getHospitalCode());
            this.getRs().updateString("INVOICE_NO", this.getInvoiceNo());
            this.getRs().updateString("Invoice_Date", this.getInvoiceDate());
            this.getRs().updateString("RECEIPT_NO", this.getReceiptNo());
            this.getRs().updateString("RECEIPT_DATE", this.getReceiptDate());
            this.getRs().updateString("billing_Sub_Group_No", this.getBillingSubGroup());
            this.getRs().updateString("order_Item_Code", this.getOrderItemCode());
            this.getRs().updateString("doctorCode", this.getDoctorCode());
            this.getRs().updateString("department_Group_Code", this.getDepartmentGroupCode());
            this.getRs().updateString("LOCATION_CODE", this.getLocationCode()); */
            this.getRs().updateDouble("AMOUNT_BEF_DISCOUNT", this.getAmountBefDiscount());
            this.getRs().updateDouble("AMOUNT_OF_DISCOUNT", this.getAmountOfDiscount());
            this.getRs().updateDouble("AMOUNT_AFT_DISCOUNT", this.getAmountAftDiscount());
//            this.getRs().updateString("BATCH_NO", this.getBatchNo()); 
////            this.getRs().updateString("UPDATE_DATE", this.getUpdateDate());
////            this.getRs().updateString("update_Time", this.getUpdateTime());
////            this.getRs().updateString("USER_ID", this.getUserId());
/*            this.getRs().updateString("OLD_DOCTOR_CODE", this.getOldDoctorCode());
            this.getRs().updateString("inv_Is_Void", this.getInvIsVoid());  
            this.getRs().updateString("receipt_Type_Code", this.getReceiptTypeCode());
            this.getRs().updateString("receipt_Module", this.getReceiptModule());
            this.getRs().updateString("receipt_Type", this.getReceiptType());
            this.getRs().updateString("rec_Is_Void", this.getRecIsVoid()); 
            this.getRs().updateDouble("rec_Amount_Bef_Discount", this.getRecAmountBefDiscount());
            this.getRs().updateDouble("rec_Amount_Of_Discount", this.getRecAmountOfDiscount()); */
            this.getRs().updateDouble("REC_PREMIUM_AMT", this.getRecPremiumAmt());
            this.getRs().updateString("RECEIPT_MODE_CODE", this.getReceiptModeCode());
            this.getRs().updateDouble("NOR_ALLOCATE_AMT", this.getNorAllocateAmt());
            this.getRs().updateDouble("NOR_ALLOCATE_PCT", this.getNorAllocatePct());
            this.getRs().updateDouble("DR_AMT", this.getDrAmt());
            this.getRs().updateDouble("DR_AMT_402", this.getDrAmt402());
            this.getRs().updateDouble("DR_AMT_406", this.getDrAmt406());
            this.getRs().updateDouble("DR_PREMIUM", this.getDrPremium());
            this.getRs().updateDouble("DR_TAX_BASE", this.getDrTaxBase());
            this.getRs().updateDouble("HP_AMT", this.getHpAmt());
            this.getRs().updateDouble("HP_PREMIUM", this.getHpPremium());
            this.getRs().updateDouble("HP_TAX", this.getHpTax());
            this.getRs().updateString("TAX_TYPE_CODE", this.getTaxTypeCode());
            this.getRs().updateString("ADMISSION_TYPE_CODE", this.getAdmissionTypeCode());
            this.getRs().updateString("DOCTOR_TREATMENT_CODE", this.getDoctorTreatmentCode());
            this.getRs().updateDouble("PREMIUM_CHARGE_PCT", this.getPremiumChargePct());
            this.getRs().updateDouble("PREMIUM_VAT_PCT", this.getPremiumVatPct());
//            this.getRs().updateString("transaction_Date", this.getTransactionDate());
            this.getRs().updateString("DOCTOR_CATEGORY_CODE", this.getDoctorCategoryCode());
            this.getRs().updateString("EXCLUDE_TREATMENT", this.getExcludeTreatment());
            this.getRs().updateDouble("PREMIUM_REC_AMT", this.getPremiumRecAmt());
            
            // update when invoice have RECEIPT_NO
            this.getRs().updateString("STATUS_MODIFY", this.getStatusModify()); 
            this.getRs().updateString("yyyy", this.getYyyy());
            this.getRs().updateString("mm", this.getMm());
            this.getRs().updateString("dd", this.getDd()); 
            this.getRs().updateString("COMPUTE_DAILY_DATE", this.getComputeDailyDate());
            this.getRs().updateString("COMPUTE_DAILY_TIME", this.getComputeDailyTime());
            this.getRs().updateString("COMPUTE_DAILY_USER_ID", this.getComputeDailyUserID());
            
            // update when compute monthly            
//            this.getRs().updateString("ACTIVE", this.getActive());
            
            // Insert the row
            this.getRs().updateRow();
            ret = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        finally {
          try {
            if (rs != null) { 
                rs.close();
                rs = null;
            }
/*            if (stmt != null) {
                stmt.close();
                stmt = null;
            } */
          }
          catch (SQLException ex)  {
            System.out.println("A SQLException error has occured in " + this.getClass().getName() + ".update() \n" + ex.getMessage());
            ex.printStackTrace();
            ret = false;
          }
        }
    
    return ret;
    }
    
    public boolean IsNotFoundDoctorCode(CalculatorFrm1 cf, String receiptDate) {
        boolean ret = true;
        String sql = "select HOSPITAL_CODE, INVOICE_NO, DOCTOR_CODE from TRN_DAILY where (RECEIPT_DATE = '" + receiptDate + "'";
                sql = sql + " or INVOICE_DATE = '" + receiptDate + "')";
                sql = sql + " and DOCTOR_CODE not in (SELECT CODE FROM DOCTOR WHERE ACTIVE = '1') ";
                sql = sql + " order by INVOICE_NO, DOCTOR_CODE";
                
        ResultSet rs = this.getDBConnection().executeQuery(sql);
        List values = new ArrayList();
            try {
                while (rs.next()) {
                    values.add(receiptDate);
                    values.add(rs.getString("INVOICE_NO"));
                    values.add("��辺����ᾷ�� : '" + rs.getString("DOCTOR_CODE") + "'");
                    cf.setTableValue(values);
                    values.clear();
                }
            } catch (SQLException e) {
                // TODO
                e.printStackTrace();
            } finally {
               //Clean up resources, close the connection.
               values = null;
               if(rs != null) {
                  try {
                     rs.close();
                     rs = null;
                    }
                  catch (Exception ignored) { ignored.printStackTrace();   }
            }
        }
        return ret;
    }
    
    
    public boolean IsNotFoundDoctorCategoryCode(CalculatorFrm1 cf, String receiptDate) {
        boolean ret = true;
        String sql = "select HOSPITAL_CODE, INVOICE_NO, DOCTOR_CODE from TRN_DAILY where (RECEIPT_DATE = '" + receiptDate + "'";
                sql = sql + " or INVOICE_DATE = '" + receiptDate + "')";
                sql = sql + " and DOCTOR_CODE in (SELECT CODE FROM DOCTOR WHERE ACTIVE = '1' and DOCTOR_CATEGORY_CODE is null) ";
                sql = sql + " order by INVOICE_NO, DOCTOR_CODE";
                
        ResultSet rs = this.getDBConnection().executeQuery(sql);
        List values = new ArrayList();
            try {
                while (rs.next()) {
                    values.add(receiptDate);
                    values.add(rs.getString("INVOICE_NO"));
                    values.add("Doctor Code : '" + rs.getString("DOCTOR_CODE") + "' �ѧ����� set category_code.");
                    cf.setTableValue(values);
                    values.clear();
                }
            } catch (SQLException e) {
                // TODO
                e.printStackTrace();
            } finally {
               //Clean up resources, close the connection.
               values = null;
               if(rs != null) {
                  try {
                     rs.close();
                     rs = null;
                    }
                  catch (Exception ignored) { ignored.printStackTrace();   }
            }
        }
        return ret;
    }
    
    public boolean IsNotFoundOrderItemCode(CalculatorFrm1 cf, String receiptDate) {
        boolean ret = true;
        String sql = "select INVOICE_NO, ORDER_ITEM_CODE from TRN_DAILY where ";
        sql = sql + " (RECEIPT_DATE = '" + receiptDate + "'";
        sql = sql + " or INVOICE_DATE = '" + receiptDate + "')";
        sql = sql + " and ORDER_ITEM_CODE not in (SELECT CODE FROM ORDER_ITEM) order by INVOICE_NO, ORDER_ITEM_CODE ";
        
        ResultSet rs = this.getDBConnection().executeQuery(sql);
        List values = new ArrayList();
            try {
                while (rs.next()) {
                    values.add(receiptDate);
                    values.add(rs.getString("INVOICE_NO"));
                    values.add("��辺 Treatment Code : '" + rs.getString("ORDER_ITEM_CODE") + "' ��� invoice �Ţ��� : '"+rs.getString("INVOICE_NO") + "'");
                    cf.setTableValue(values);
                    values.clear();
                }
            } catch (SQLException e) {
                // TODO
                e.printStackTrace();
            } finally {
               //Clean up resources, close the connection.
               values = null;
               if(rs != null) {
                  try {
                     rs.close();
                     rs = null;
                    }
                  catch (Exception ignored) { ignored.printStackTrace();   }
            }
        }
        return ret;
    }        
    
    
    public boolean IsNotFoundDepartmentCode(CalculatorFrm1 cf, String receiptDate) {
        boolean ret = true;
        String sql = "select HOSPITAL_CODE, INVOICE_NO, DEPARTMENT_CODE from TRN_DAILY where (RECEIPT_DATE = '" + receiptDate + "'";
                sql = sql + " or INVOICE_DATE = '" + receiptDate + "')";
                sql = sql + " and DEPARTMENT_CODE not in (SELECT CODE FROM DEPARTMENT_GROUP) order by INVOICE_NO, DEPARTMENT_CODE";
                
        ResultSet rs = this.getDBConnection().executeQuery(sql);
        List values = new ArrayList();
            try {
                while (rs.next()) {
                    values.add(receiptDate);
                    values.add(rs.getString("INVOICE_NO"));
                    values.add("��辺 Department Code : '" + rs.getString("DEPARTMENT_CODE") + "'");
                    cf.setTableValue(values);
                    values.clear();
                }
            } catch (SQLException e) {
                // TODO
                e.printStackTrace();
            } finally {
               //Clean up resources, close the connection.
               values = null;
               if(rs != null) {
                  try {
                     rs.close();
                     rs = null;
                    }
                  catch (Exception ignored) { ignored.printStackTrace();   }
            }
        }
        return ret;
    }    
    
    public boolean IsNotFoundReceiptTypeCode(CalculatorFrm1 cf, String receiptDate) {
        boolean ret = true;
        String sql = "select HOSPITAL_CODE, INVOICE_NO, RECEIPT_TYPE_CODE from TRN_DAILY where (RECEIPT_DATE = '" + receiptDate + "'";
                sql = sql + " or INVOICE_DATE = '" + receiptDate + "')";
                sql = sql + " and RECEIPT_TYPE_CODE not in (SELECT CODE FROM RECEIPT_TYPE) order by INVOICE_NO, RECEIPT_TYPE_CODE";
                
        ResultSet rs = this.getDBConnection().executeQuery(sql);
        List values = new ArrayList();
            try {
                while (rs.next()) {
                    values.add(receiptDate);
                    values.add(rs.getString("INVOICE_NO"));
                    values.add("��辺 Receipt type : '" + rs.getString("RECEIPT_TYPE_CODE") + "'");
                    cf.setTableValue(values);
                    values.clear();
                }
            } catch (SQLException e) {
                // TODO
                e.printStackTrace();
            } finally {
               //Clean up resources, close the connection.
               values = null;
               if(rs != null) {
                  try {
                     rs.close();
                     rs = null;
                    }
                  catch (Exception ignored) { ignored.printStackTrace();   }
            }
        }
        return ret;
    }    
    
    public boolean IsNotFoundPaymentModeCode(CalculatorFrm1 cf, String receiptDate) {
        boolean ret = true;
        String sql = "select HOSPITAL_CODE, INVOICE_NO, DOCTOR_CODE from TRN_DAILY where (RECEIPT_DATE = '" + receiptDate + "'";
                sql = sql + " or INVOICE_DATE = '" + receiptDate + "')";
                sql = sql + " and DOCTOR_CODE in (SELECT CODE FROM DOCTOR WHERE ACTIVE = '1' and PAYMENT_MODE_CODE is null) ";
                sql = sql + " order by INVOICE_NO, DOCTOR_CODE";
                
        ResultSet rs = this.getDBConnection().executeQuery(sql);
        List values = new ArrayList();
            try {
                while (rs.next()) {
                    values.add(receiptDate);
                    values.add(rs.getString("INVOICE_NO"));
                    values.add("Doctor Code : '" + rs.getString("DOCTOR_CODE") + "' �ѧ����� set payment mode.");
                    cf.setTableValue(values);
                    values.clear();
                }
            } catch (SQLException e) {
                // TODO
                e.printStackTrace();
            } finally {
               //Clean up resources, close the connection.
               values = null;
               if(rs != null) {
                  try {
                     rs.close();
                     rs = null;
                    }
                  catch (Exception ignored) { ignored.printStackTrace();   }
            }
        }
        return ret;
    }
    
    
    public boolean rollBackUpdate(String hospitalCode, String yyyy, String mm, String dd) {
        List sqlCommand = new ArrayList();
        boolean ret = false;
        String sql1 = "update TRN_DAILY set ISLOADED = 'N'";
                sql1 = sql1 + " where INVOICE_DATE = '" + yyyy.concat(mm).concat(dd) + "'";
                sql1 = sql1 + " and HOSPITAL_CODE = '" + hospitalCode + "'";
        sqlCommand.add( sql1 );
        ret = super.rollBack(sqlCommand);
        return ret;
    }
    
    public boolean rollBackDelete(String hospitalCode, String yyyy, String mm, String dd) {
        List sqlCommand = new ArrayList();
        boolean ret = false;
        String sql1 = "delete from TRN_DAILY ";
                sql1 = sql1 + " where INVOICE_DATE = '" + yyyy.concat(mm).concat(dd) + "'";
                sql1 = sql1 + " and HOSPITAL_CODE = '" + hospitalCode + "'";
        sqlCommand.add( sql1 );
        ret = super.rollBack(sqlCommand);
        return ret;
    }
    public boolean rollBackDeleteByReceiptDate(String hospitalCode, String yyyy, String mm, String dd) {
        List sqlCommand = new ArrayList();
        boolean ret = false;
        String sql1 = "delete from TRN_DAILY ";
                sql1 = sql1 + " where TRANSACTION_DATE = '" + yyyy.concat(mm).concat(dd) + "'";
//                sql1 = sql1 + " where (RECEIPT_DATE = '" + yyyy.concat(mm).concat(dd) + "'";
//                sql1 = sql1 + " or INVOICE_DATE = '" + yyyy.concat(mm).concat(dd) + "')";
                sql1 = sql1 + " and HOSPITAL_CODE = '" + hospitalCode + "'";
                sql1 = sql1 + " and INVOICE_DATE > '20071100'";
        sqlCommand.add( sql1 );
        ret = super.rollBack(sqlCommand);
        return ret;
    }
    // delete by receipt date
    public boolean rollBackUpdateByReceiptDate(String hospitalCode, String yyyy, String mm, String dd) {
        List sqlCommand = new ArrayList();
        boolean ret = false;        
        
        String sql1 = "update TRN_DAILY set RECEIPT_NO = '9999', RECEIPT_DATE = '99999999', RECEIPT_TYPE = ''";
                sql1 = sql1 + ", REC_AMOUNT_BEF_DISCOUNT = 0";
                sql1 = sql1 + ", REC_AMOUNT_OF_DISCOUNT = 0, RECEIPT_TYPE_CODE = '', UPDATE_DATE = '', UPDATE_TIME = '', RECEIPT_MODULE = 'CASH' ";
                sql1 = sql1 + " where RECEIPT_DATE = '" + yyyy.concat(mm).concat(dd) + "'";
//                sql1 = sql1 + " and RECEIPT_MODULE = 'AR'";
                sql1 = sql1 + " and HOSPITAL_CODE = '" + hospitalCode + "'";
                sql1 = sql1 + " and INVOICE_DATE < '20071100'";
        sqlCommand.add( sql1 );
        ret = super.rollBack(sqlCommand);
        return ret;
    }
    
    public boolean rollBackUpdateCompute(String hospitalCode, String yyyy, String mm, String dd) {
        List sqlCommand = new ArrayList();
        boolean ret = false;
        
        String sql1 = "update TRN_DAILY set REC_PREMIUM_AMT = 0, ";
                sql1 = sql1 + " RECEIPT_MODE_CODE = '', ";
                sql1 = sql1 + " NOR_ALLOCATE_AMT = 0, ";
                sql1 = sql1 + " NOR_ALLOCATE_PCT = 0, ";
                sql1 = sql1 + " DR_AMT = 0, ";
                sql1 = sql1 + " DR_AMT_402 = 0, ";
                sql1 = sql1 + " DR_AMT_406 = 0, ";
                sql1 = sql1 + " DR_PREMIUM = 0, ";
                sql1 = sql1 + " DR_TAX_BASE = 0, ";
                sql1 = sql1 + " HP_AMT = 0, ";
                sql1 = sql1 + " HP_PREMIUM = 0, ";
                sql1 = sql1 + " HP_TAX = 0, ";
                sql1 = sql1 + " TAX_TYPE_CODE = '', ";
                sql1 = sql1 + " DOCTOR_TREATMENT_CODE = '', ";
                sql1 = sql1 + " PREMIUM_CHARGE_PCT = 0, ";
                sql1 = sql1 + " PREMIUM_VAT_PCT = 0, ";
                sql1 = sql1 + " DOCTOR_CATEGORY_CODE = '', ";
                sql1 = sql1 + " EXCLUDE_TREATMENT = '', ";
                sql1 = sql1 + " PREMIUM_REC_AMT = 0, ";
                sql1 = sql1 + " STATUS_MODIFY = 'R', ";
                sql1 = sql1 + " YYYY = '', ";
                sql1 = sql1 + " MM = '', ";
                sql1 = sql1 + " DD = '', ";
                sql1 = sql1 + " COMPUTE_DAILY_DATE = '', ";
                sql1 = sql1 + " COMPUTE_DAILY_TIME = '', ";
                sql1 = sql1 + " COMPUTE_DAILY_USER_ID = '', ";
                // ����᷹�ѹ�Ѵ��ҧ ���ФԴ��ҹ�Ҩ�¡��ԡ��� TRANSACTION_DATE �ҡ����
                sql1 = sql1 + " where TRANSACTION_DATE = '" + yyyy.concat(mm).concat(dd) + "'";
                sql1 = sql1 + " and INVOICE_DATE > '20071100'";
/*
                sql1 = sql1 + " where (RECEIPT_DATE = '" + yyyy.concat(mm).concat(dd) + "')";
//                sql1 = sql1 + " or INVOICE_DATE = '" + yyyy.concat(mm).concat(dd) + "')";
                sql1 = sql1 + " and HOSPITAL_CODE = '" + hospitalCode + "'";
                sql1 = sql1 + " and RECEIPT_DATE = INVOICE_DATE";
                sql1 = sql1 + " or (INVOICE_DATE = '" + yyyy.concat(mm).concat(dd) + "' and RECEIPT_NO = '" + Status.RECEIPT_NO_DEFAULT + "')";
                sql1 = sql1 + " and INVOICE_DATE > '20071100'";
*/                
        sqlCommand.add( sql1 );
        ret = super.rollBack(sqlCommand);
        return ret;
    }
    
    // delete by receipt date
    public boolean rollBackUpdateByReceiptDateWithOldData(String hospitalCode, String yyyy, String mm, String dd) {
        List sqlCommand = new ArrayList();
        boolean ret = false;        
        
        String sql1 = "update TRN_DAILY set RECEIPT_NO = '9999', RECEIPT_DATE = '99999999', RECEIPT_TYPE = ''";
                sql1 = sql1 + ", REC_AMOUNT_BEF_DISCOUNT = 0";
                sql1 = sql1 + ", REC_AMOUNT_OF_DISCOUNT = 0, RECEIPT_TYPE_CODE = 'AR', UPDATE_DATE = '', UPDATE_TIME = '', RECEIPT_MODULE = 'CASH' ";
                sql1 = sql1 + " where RECEIPT_DATE = '" + yyyy.concat(mm).concat(dd) + "'";
                sql1 = sql1 + " and RECEIPT_MODULE = 'AR'";
                sql1 = sql1 + " and HOSPITAL_CODE = '" + hospitalCode + "'";
                sql1 = sql1 + " and INVOICE_DATE < '20071100'";
        sqlCommand.add( sql1 );
        ret = super.rollBack(sqlCommand);
        return ret;
    }
    
    public int updateAdmissionTypeI(String computeDate) {
        int ret = -1;
        String sql1 = "update TRN_DAILY set ADMISSION_TYPE_CODE = 'I'";
                sql1 = sql1 + " where TRANSACTION_DATE = '" + computeDate + "'";
                sql1 = sql1 + " and substr(INVOICE_NO,2,1) = 'I'";
                sql1 = sql1 + " and INVOICE_DATE > '20071100'"; 
        ret = conn.executeUpdate(sql1);
        return ret;
    }
    public int updateAdmissionTypeO(String computeDate) {
        int ret = -1;
        String sql1 = "update TRN_DAILY set ADMISSION_TYPE_CODE = 'O'";
                sql1 = sql1 + " where TRANSACTION_DATE = '" + computeDate + "'";
                sql1 = sql1 + " and substr(INVOICE_NO,2,1) <> 'I'";
                sql1 = sql1 + " and INVOICE_DATE > '20071100'"; 
        ret = conn.executeUpdate(sql1);
        return ret;
    }
    
    public int updateReceiptTypeCoupon(String computeDate) {
        int ret = -1;
        String sql1 = "update TRN_DAILY set RECEIPT_TYPE_CODE = '1', RECEIPT_NO = INVOICE_NO, ";
                sql1 = sql1 + "RECEIPT_DATE = INVOICE_DATE, ";
                sql1 = sql1 + "RECEIPT_MODULE = 'CASH'";
                sql1 = sql1 + " where TRANSACTION_DATE = '" + computeDate + "'";
                sql1 = sql1 + " and RECEIPT_NO = '9999'";
                sql1 = sql1 + " and RECEIPT_TYPE_CODE = 'AR'";
                sql1 = sql1 + " and INVOICE_NO like 'R%'";
                sql1 = sql1 + " and INVOICE_DATE > '20071100'";
        ret = conn.executeUpdate(sql1);
        return ret;
    }
    
    // insert into temp table ੾����¡��ᾷ����Ө��¨�ԧ
    public int insertTempTransactionDetail(String tableName, String doctorCode, String yyyy, String mm, String hospitalCode) {
        int ret = -1;
        String sql = "HOSPITAL_CODE, INVOICE_NO, INVOICE_DATE, BILLING_SUB_GROUP_NO, ORDER_ITEM_CODE, DOCTOR_CODE, ORDER_DATE, ";
        sql = sql + "DEPARTMENT_GROUP_CODE, LOCATION_CODE, ADMISSION_TYPE_CODE, AMOUNT_BEF_DISCOUNT, AMOUNT_OF_DISCOUNT, ";
        sql = sql + "AMOUNT_AFT_DISCOUNT, BATCH_NO, STATUS_MODIFY, UPDATE_DATE, UPDATE_TIME, USER_ID, OLD_DOCTOR_CODE, ";
        sql = sql + "INV_IS_VOID, YYYY, MM, DD, RECEIPT_NO, RECEIPT_DATE, RECEIPT_MODULE, RECEIPT_TYPE, RECEIPT_TYPE_CODE, ";
        sql = sql + "RECEIPT_MODE_CODE, REC_IS_VOID, REC_AMOUNT_BEF_DISCOUNT, REC_AMOUNT_OF_DISCOUNT, REC_PREMIUM_AMT, ";
        sql = sql + "NOR_ALLOCATE_AMT, NOR_ALLOCATE_PCT, DR_AMT, DR_AMT_402, DR_AMT_406, DR_PREMIUM, DR_TAX_BASE, ";
        sql = sql + "HP_AMT, HP_PREMIUM, HP_TAX, TAX_TYPE_CODE, DOCTOR_TREATMENT_CODE, COMPUTE_DAILY_DATE, COMPUTE_DAILY_TIME, ";
        sql = sql + "COMPUTE_DAILY_USER_ID, PREMIUM_CHARGE_PCT, PREMIUM_VAT_PCT, TRANSACTION_DATE, DOCTOR_CATEGORY_CODE, ";
        sql = sql + "EXCLUDE_TREATMENT, PREMIUM_REC_AMT, ";
        sql = sql + "ACTIVE, INVOICE_TYPE, TREATMENT_TYPE, TOTAL_BILL_AMOUNT, TOTAL_DR_REC_AMOUNT, OLD_AMOUNT, DOCTOR_CODE_GL, ";
        sql = sql + "CHANGE_DOCTOR_CODE, HN_NO, VN_NO, HN_NAME, DR_AMT_GL";
        
        String sql1 = "select " + sql + " ";
        sql1 = sql1 + " from TRN_DAILY " + 
//                     " where DOCTOR_CODE='" + this.getDoctorCode() + "'" +
                        " where HOSPITAL_CODE ='" + this.getHospitalCode() + "'" + 
                        " and (BATCH_NO is null or BATCH_NO = '')" + 
                        " and STATUS_MODIFY = '" + Status.STATUS_CALCULATED + "'" +
//                        " and (STATUS_MODIFY <> '" + Status.STATUS_CALCULATED + "' or STATUS_MODIFY is null)" +
                        " and INVOICE_TYPE <> '" + Status.INVOICE_TYPE_IS_ACCU + "'" +
                        " and YYYY='" + Batch.getYyyy() + "' and MM='" + Batch.getMm() + "'" +
                        " and DOCTOR_CODE is not null " +
                        " and ACTIVE = '1' " +
                        " and RECEIPT_NO <> '" + Status.RECEIPT_NO_DEFAULT + "'" +
                        " and COMPUTE_DAILY_DATE is not null";
        ret = conn.executeUpdate(sql1);
        return ret;
    }  
    
}
